<?php
namespace App\Model;

use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

class FirstUserRecharge extends BaseModel
{
    protected $table = 'first_recharge_user_log';//用户首充记录log
    protected $luck_draw_user_prop_table = 'luck_draw_user_prop';//抽奖道具
    protected $finance_diamon_stream = 'finance_diamond_stream';//钻石流水
    protected $finance_order_recharge = 'finance_recharge_order';//充值订单表
    protected $connection = 'api';//数据库地址
    protected $activity_connection = 'activity';
    protected $activity_first_recharge_send_log = 'first_recharge_send_log';//礼物装扮背包

    /**
     * 获取当日统计新用户数
     *
     * @param integer $page
     * @param integer $limit
     */
    public function getDailyRechargeList()
    {
        $model = DB::connection($this->connection)->table($this->table)
        ->select(DB::raw('COUNT(DISTINCT user_id) as usernums,create_time'))
        ->groupBy('create_time')
        ->orderBy('create_time','desc')
        ->limit(30)
        ->get();
        $model = objToArray($model);
        return $model;
    }
    /**
     * 获取指定日期下的领取券统计数
     */
    public function getDailyTicketNums($datetime)
    {
        $model = DB::connection($this->connection)->table($this->table)
        ->where('has_ticket',1)
        ->where('create_time',$datetime)
        ->count();
        return $model; 
    }
    /**
     * 抽奖券使用数
     */
    public function getDailyTicketUseCount($datetime)
    {
        $model = DB::connection($this->connection)->table($this->table)
        ->where('create_time',$datetime)
        ->get();
        $model = objToArray($model);
        $count=0;
        foreach($model as $v)
        {
            $count += DB::connection($this->connection)->table($this->luck_draw_user_prop_table)->where('id',$v['luck_draw_user_prop_id'])->where(['used'=>1])->count();
        }
        return $count;
    }
    /**
     * 获取消耗钻石数
     */
    public function getUsedDiamonValue($datetime)
    {
        $model = DB::connection($this->connection)->table($this->table)
        ->where('create_time',$datetime)
        ->get();
        $model = objToArray($model);
        $count=0;
        $startDay = Carbon::parse($datetime)->startOfDay();
        $endDay = Carbon::parse($datetime)->endOfDay();
        foreach($model as $v)
        {
            $count += DB::connection($this->connection)->table($this->finance_diamon_stream)->where(['user_id'=>$v['user_id'],'diamond_type'=>6])->whereBetween('change_time',[$startDay,$endDay])->sum('diamond_value');
        }
        return abs($count);
    }
    /**
     * 兑换金豆数
     */
    public function getUsedGoldValue($datetime)
    {
        $model = DB::connection($this->connection)->table($this->table)
        ->where('create_time',$datetime)
        ->get();
        $model = objToArray($model);
        $count=0;
        $startDay = Carbon::parse($datetime)->startOfDay();
        $endDay = Carbon::parse($datetime)->endOfDay();
        foreach($model as $v)
        {
            $count += DB::connection($this->connection)->table($this->finance_diamon_stream)->where(['user_id'=>$v['user_id'],'diamond_type'=>100])->whereBetween('change_time',[$startDay,$endDay])->sum('diamond_value');
        }
        return abs($count);
    }
    /**
     * 次日充值用户
     */
    public function getTomorrowUserCount($datetime)
    {
        $afterDays = Carbon::parse($datetime)->addDays(-1)->toDateString();//前一天
        $userids = DB::connection($this->connection)->table($this->table)
        ->where('create_time',$afterDays)
        ->get(['user_id']);
        $userids = objToArray($userids);
        $userids = array_column($userids,'user_id');

        $toDay = Carbon::parse($datetime);
        $toStartDay = Carbon::parse($toDay)->startOfDay();//开始时间
        $toEndDay = Carbon::parse($toDay)->endOfDay();//结束时间
        //第二天中前一天用户中充值记录
        $res = DB::connection($this->connection)->table($this->finance_order_recharge)->select('user_id')->whereIn('user_id',$userids)->where('status',1)->whereBetween('create_time',[$toStartDay,$toEndDay])->distinct()->get();
        $res = objToArray($res);
        $count = count($res);
        return $count;
    }
    /**
     * 统计前N天留存用户的充值数
     */
    public function getUserRecharge($datetime,$day)
    {
        $beforeThreeDays = Carbon::parse($datetime)->addDays(-$day)->toDateString();//统计前N天的用户
        $userids = DB::connection($this->connection)->table($this->table)
        ->where('create_time','>=',$beforeThreeDays)
        ->where('create_time','<',$datetime)
        ->get(['user_id']);
        $userids = objToArray($userids);
        $userids = array_column($userids,'user_id');

        $startDay = Carbon::parse($beforeThreeDays)->startOfDay();//开始时间
        $endDay = Carbon::parse($datetime)->addDays(-1)->endOfDay();//结束时间
        //使用许愿卡人数
        $yestordayUsedTicketInfo = DB::connection($this->connection)->table($this->luck_draw_user_prop_table)->select('user_id')->whereIn('user_id',$userids)->where('used',1)->whereBetween('created_at',[$startDay,$endDay])->distinct()->get();
        $yestordayUsedTicketInfo = objToArray($yestordayUsedTicketInfo);
        $yestordayUsers = array_column($yestordayUsedTicketInfo,'user_id');
        $yestordayCount = count($yestordayUsers);

        
        $toDay = Carbon::parse($datetime);
        $toStartDay = Carbon::parse($toDay)->startOfDay();//开始时间
        $toEndDay = Carbon::parse($toDay)->endOfDay();//结束时间
        $res = DB::connection($this->connection)->table($this->finance_order_recharge)->select('user_id')->whereIn('user_id',$userids)->where('status',1)->whereBetween('create_time',[$toStartDay,$toEndDay])->distinct()->get();
        $res = objToArray($res);
        $count = count($res);

        //统计许愿池这些用户是否充值数
        // $beforecount = DB::connection($this->connection)->table($this->finance_order_recharge)->select('user_id')->whereIn('user_id',$yestordayUsers)->where('status',1)->whereBetween('create_time',[$startDay,$endDay])->distinct()->get();
        // $beforecount = objToArray($beforecount);
        // $beforecount = count($beforecount);
        $beforecount = DB::connection($this->connection)->table($this->table)
        ->where('has_ticket',1)
        ->whereBetween('create_time',[$startDay,$endDay])
        ->count();

        $moneyRes = $res = DB::connection($this->connection)->table($this->finance_order_recharge)->whereIn('user_id',$userids)->where('status',1)->whereBetween('create_time',[$startDay,$endDay])->sum('price');
        $data = [
            'count'=> empty($beforecount) ? 1 : $beforecount,
            'savecount'=> $count,
            'money' => round($moneyRes/100),
            'aftercount'=>$yestordayCount
        ];
        return $data;
    }

    /**
     * 获取首充用户送礼装扮数据统计
     */
    public function getFirstRechargeSendLog(){
        $res = DB::connection($this->activity_connection)->table($this->activity_first_recharge_send_log)->select(DB::raw('COUNT(DISTINCT user_id) as usernums,FROM_UNIXTIME(date,"%Y-%m-%d") as date,CEILING(SUM(order_price)/100) as allprice,COUNT(send_level) as sendNums,COUNT(user_type = 1 or null) as newUserNums'))
        ->groupBy('date')
        ->limit(200)
        ->get();
        $model = objToArray($res);
        return $model;
    }
}